from common import getConnection, _process_unicode

from datetime import datetime, timedelta



def GetInfoOfMatch(IDMatch):	

	query = "select homeScore, awayScore,\
		ID_Home, ID_Away,\
		date, stadium, kickOff, gameWeek,\
		(select nameTeam from Teams where ID_Team = ID_Home) as homeName,\
		(select nameTeam from Teams where ID_Team = ID_Away) as awayName,\
		(select nameLeague from Leagues where Leagues.ID_League = Matches.ID_League) as competition,\
		(penHomeScore) as penHome, (penAwayScore) as penAway,\
		ID_League\
		from Matches\
		where ID_Match = " + str(IDMatch)
	
	conn = getConnection()
	cur = conn.cursor()
	cur.execute(query)	
	row = cur.fetchone()
	listinfo = {}	
	if row is not None:
		listinfo['homeScore'] = row[0]
		listinfo['awayScore'] = row[1]		
		listinfo['IDHome'] = row[2]
		listinfo['IDAway'] = row[3]
		listinfo['date'] = str(row[4])
		listinfo['stadium'] = _process_unicode(row[5])
		listinfo['kickOff'] = str(row[6])
		listinfo['gameWeek'] = row[7]
		listinfo['homeName'] = _process_unicode(row[8])
		listinfo['awayName'] = _process_unicode(row[9])
		listinfo['competition'] = _process_unicode(row[10])
		listinfo['penHome'] = row[11]
		listinfo['penAway'] = row[12]
		listinfo['IDLeague'] = row[13]
	#	row = cur.fetchone()
	cur.close()
	conn.close()	
	return listinfo

def GetSquadOfMatch(IDMatch):

    	query = """select `order`,ID_Team, ID_Player, shiftNumber,\
	 (select alias from Players where Players.ID_Player = Squads.ID_Player)as name\
	 from Squads\
	 where ID_Match = """
	query += str(IDMatch)

	conn = getConnection()
	cur = conn.cursor()
	cur.execute(query)	
	row = cur.fetchone()
	listinfo = []
	while row is not None:
		info = {}	
		info['order'] = row[0]
		info['ID_Team'] = row[1]
		info['ID_Player'] = row[2]
		info['shiftNumber'] = row[3]
		info['name'] = _process_unicode(row[4])
		listinfo.append(info)
		row = cur.fetchone()
	cur.close()
	conn.close()	
	return listinfo

def GetHighlightOfMatch(IDMatch):		
	query = "select gameMinute,\
			(select kind from Kind_Highlights where Highlights.ID_Kind = Kind_Highlights.ID_Kind) as kind,\
			ID_Player,\
		 	(select alias from Players where Players.ID_Player = Highlights.ID_Player) as player,\
		 	(select alias from Players where Players.ID_Player = Highlights.ID_Sub_Player) as sub_player\
		 	from Highlights\
		 	where ID_Match = " + str(IDMatch);
	#print query

	conn = getConnection()		 
	cur = conn.cursor()
	cur.execute(query)	
	row = cur.fetchone()
	listinfo = []
	while row is not None:
		info = {}	
		info['gameMinute'] = row[0]
		info['kind'] = row[1]
		info['ID_Player'] = row[2]
		info['player'] = _process_unicode(row[3])
		info['sub_player'] = _process_unicode(row[4])
		listinfo.append(info)
		row = cur.fetchone()
	cur.close()
	conn.close()	
	return listinfo

# Get list of matches in (current) day, for each league or all leagues, order by kickOff
def GetListOfMatch(date = "", IDLeague = -1):

	query = "SELECT ID_League, `date`,\
		 (SELECT nameTeam FROM Teams WHERE ID_Team = ID_Home) AS homeName,\
		 (SELECT nameTeam FROM Teams WHERE ID_Team = ID_Away) AS awayName,\
		 homeScore,awayScore,\
		 ID_Match, ID_Home, ID_Away,\
		 (SELECT nameLeague FROM Leagues WHERE Leagues.ID_League = Matches.ID_League) AS competition,\
		 kickOff,\
		 penHomeScore, penAwayScore \
		 FROM Matches WHERE "

	if date != "":
			query += "`date` = '" + str(date) + "'"
	else:
            myTime = datetime.utcnow() + timedelta(hours=1)
            myToday = datetime.strftime(myTime, '%Y-%m-%d')
            query += "`date` = '" + str(myToday) + "'"

	if IDLeague  != -1:
		query += "AND ID_League = " + str(IDLeague)
	query += " ORDER BY kickOff"
	#print query

	conn = getConnection()
	cur = conn.cursor()
	cur.execute(query)	
	row = cur.fetchone()
	listinfo = []
        while row is not None:

            info = {}
            info['ID_League'] = row[0]
            info['date'] = str(row[1])
            info['homeName'] = _process_unicode(row[2])
            info['awayName'] = _process_unicode(row[3])
            info['homeScore'] = row[4]
            info['awayScore'] = row[5]
            info['ID_Match'] = row[6]
            info['ID_Home'] = row[7]
            info['ID_Away'] = row[8]
            info['competition'] = _process_unicode(row[9])
            info['kickOff'] = str(row[10])
            info['penalties'] = row[11] - row[12]
            listinfo.append(info)
            row = cur.fetchone()
	cur.close()
	conn.close()	
	return listinfo

#def date_to_string(d):
#    return datetime.strptime(data.encode('ascii', 'ignore'),"%y-%m-%d %H:%M")
